﻿<%@ WebHandler Language="C#" Class="ContainerStepPlanReport" %>

using System;
using System.IO;
using System.Web;
using Newtonsoft.Json;
using Newtonsoft.Json.Linq;
using Newtonsoft.Json.Converters;
using System.Collections.Generic;
using System.Data;
using System.Text;
using System.Net;
using ReportBusiness;

/**
 * Description: 工序排产完成情况报表
 * Copyright (c) : 通力凯顿（北京）系统集成有限公司
 * Writer:zhunan
 * Date:2022.7.7
 */

public class ContainerStepPlanReport : IHttpHandler
{
    uMESReportOracleDAL dal = new uMESReportOracleDAL();
    public bool IsReusable { get { return false; } }
    public void ProcessRequest(HttpContext context)
    {
        string requestjson = "";
        using (StreamReader reader = new StreamReader(context.Request.InputStream)) { requestjson = reader.ReadToEnd(); }
        JObject jObject = JsonConvert.DeserializeObject(requestjson) as JObject;
        if (jObject["type"] != null)
        {
            try
            {
                Result r = new Result();
                context.Response.ContentType = "text/json";
                string type = jObject["type"].ToString();
                switch (type)
                {
                    case "getData":
                        r = getData(jObject);//查询
                        context.Response.Write(JsonConvert.SerializeObject(r));
                        break;
                    case "getCompute":
                        r = getCompute(jObject);//统计数据
                        context.Response.Write(JsonConvert.SerializeObject(r));
                        break;
                    case "exportExcel":
                        exportExcel(jObject);//导出
                        break;
                    default:
                        break;
                }
            }
            catch (Exception e)
            {
                Result r = new Result();
                r.data = null;
                r.meta = new Result.Meta(500, e.Message);
                r.total = 0;
                context.Response.ContentType = "text/json";
                context.Response.Write(JsonConvert.SerializeObject(r));
            }
        }
    }

    string getSearchSqlString(JObject jObject)
    {
        string sql = @"
SELECT  cci.containerno,cci.containername,cci.productname,cci.productdescription,cci.fromworkcentername workcentername,
        csd.plannedfinishdate,cci.workflowstepname currentspec,csd.stepplancreatedate,
        ws.workflowstepname,ws.sequence,
        NVL(NVL(t.checkDate,cfc.createdate),csr.recivedate) checkDate,
        e.fullname stepplancreateemployeename,
        CASE WHEN NVL(NVL(t.checkDate,cfc.createdate),csr.recivedate) IS NULL AND TRUNC(SYSDATE) - TRUNC(csd.plannedfinishdate) > 0 THEN 1
             WHEN NVL(NVL(t.checkDate,cfc.createdate),csr.recivedate) IS NULL AND TRUNC(SYSDATE) - TRUNC(csd.plannedfinishdate) <= 0 THEN 0
             WHEN NVL(NVL(t.checkDate,cfc.createdate),csr.recivedate) IS NOT NULL 
                  AND TRUNC(NVL(NVL(t.checkDate,cfc.createdate),csr.recivedate)) - TRUNC(csd.plannedfinishdate) <= 0 THEN 0
             WHEN NVL(NVL(t.checkDate,cfc.createdate),csr.recivedate) IS NOT NULL 
                  AND TRUNC(NVL(NVL(t.checkDate,cfc.createdate),csr.recivedate)) - TRUNC(csd.plannedfinishdate) > 0 THEN 1
        END status,
        CASE WHEN NVL(NVL(t.checkDate,cfc.createdate),csr.recivedate) IS NOT NULL THEN 1 ELSE 0 END isFinish,
        CASE WHEN NVL(NVL(t.checkDate,cfc.createdate),csr.recivedate) IS NOT NULL 
                  AND TRUNC(NVL(NVL(t.checkDate,cfc.createdate),csr.recivedate)) - TRUNC(csd.plannedfinishdate) > 0
             THEN 1 ELSE 0 
        END isDelayFinish,
        e2.fullname startPerson,
        CASE WHEN NVL(NVL(t.checkDate,cfc.createdate),csr.recivedate) IS NOT NULL 
            THEN CASE WHEN TRUNC(NVL(NVL(t.checkDate,cfc.createdate),csr.recivedate)) - TRUNC(csd.plannedfinishdate) < 0 THEN 0
                 ELSE TRUNC(NVL(NVL(t.checkDate,cfc.createdate),csr.recivedate)) - TRUNC(csd.plannedfinishdate) END
            WHEN NVL(NVL(t.checkDate,cfc.createdate),csr.recivedate) IS NULL
            THEN TRUNC(SYSDATE) - TRUNC(csd.plannedfinishdate) 
       END delaydays
FROM containercurrentinfo cci
LEFT JOIN factory f ON f.factoryid = cci.originalfactoryid
INNER JOIN containerstepplan csd ON csd.containerid = cci.containerid AND csd.plantype = 1
LEFT JOIN workflowstep ws ON ws.workflowstepid = csd.workflowstepid
LEFT JOIN (
            SELECT csr.containerid,csr.workflowstepid,MAX(csr.checkdate) checkDate
            FROM containersteprecord csr
            LEFT JOIN containercurrentinfo cci ON cci.containerid = csr.containerid 
            WHERE 1=1 ";
        //件号
        if (jObject["productname"] != null && jObject["productname"].ToString() != "")
        {
            sql += string.Format(" AND cci.productname = '{0}'", jObject["productname"].ToString());
        }

        //流水卡号
        if (jObject["containername"] != null && jObject["containername"].ToString() != "")
        {
            sql += string.Format(" AND cci.containername = '{0}'", jObject["containername"].ToString());
        }
        sql += @"
            GROUP BY csr.containerid,csr.workflowstepid 
        ) t ON t.containerid = cci.containerid AND t.workflowstepid = ws.workflowstepid
LEFT JOIN containerfinishedcheckinfo cfc ON cfc.containerid = csd.containerid AND csd.workflowstepid = cfc.workflowstepid AND cfc.isuse = 0
LEFT JOIN (
            SELECT crrd.workflowstepid,crr.containerid,crr.recivedate,crr.isuse
            FROM containershiprecive crr
            LEFT JOIN containershiprecordstep crrd ON crrd.containershiprecordid = crr.containershiprecordid
            LEFT JOIN containercurrentinfo cci ON cci.containerid = crr.containerid
            WHERE crr.isuse = 0 ";
        //件号
        if (jObject["productname"] != null && jObject["productname"].ToString() != "")
        {
            sql += string.Format(" AND cci.productname = '{0}'", jObject["productname"].ToString());
        }

        //流水卡号
        if (jObject["containername"] != null && jObject["containername"].ToString() != "")
        {
            sql += string.Format(" AND cci.containername = '{0}'", jObject["containername"].ToString());
        }
        sql += @"
        ) csr ON csr.containerid = cci.containerid AND csr.workflowstepid = ws.workflowstepid AND csr.isuse = 0
LEFT JOIN employee e ON e.employeeid = csd.stepplancreateemployeeid
LEFT JOIN container c ON c.containerid = cci.containerid 
LEFT JOIN employee e2 ON e2.employeeid = c.startemployeeid
LEFT JOIN specbase sb ON sb.specbaseid = ws.specbaseid
LEFT JOIN spec s ON s.specid = NVL(sb.revofrcdid,ws.specid)
LEFT JOIN containerstepnoship cno ON cno.containerid = cci.containerid AND cno.workflowstepid = csd.workflowstepid AND cno.noship = 2 AND cno.isuse = 0 
LEFT JOIN containercommidetail ccm ON ccm.containerid = csd.containerid AND ccm.workflowstepid = csd.workflowstepid
WHERE cci.containerstatus = 1 
      AND cno.containerstepnoshipid IS NULL AND ccm.containercommidetailid IS NULL AND NVL(s.controlcode,'LY01') <> 'LY05'
      AND ((cci.isinstore = 1 AND cci.workflowid <> ws.workflowid) OR (cci.isinstore = 0 AND cci.workflowid = ws.workflowid))";
        //排除: 排产工序版本和当前批次工艺版本不一致数据
        // 主制分厂
        if (jObject["factoryid"] != null && jObject["factoryid"].ToString() != "")
        {
            sql += string.Format(" AND cci.originalfactoryid = '{0}'", jObject["factoryid"].ToString());
        }

        // 主制工区
        if (jObject["workcenterid"] != null && jObject["workcenterid"].ToString() != "")
        {
            sql += string.Format(" AND cci.fromworkcenterid = '{0}'", jObject["workcenterid"].ToString());
        }

        //件号
        if (jObject["productname"] != null && jObject["productname"].ToString() != "")
        {
            sql += string.Format(" AND cci.productname = '{0}'", jObject["productname"].ToString());
        }

        //流水卡号
        if (jObject["containername"] != null && jObject["containername"].ToString() != "")
        {
            sql += string.Format(" AND cci.containername = '{0}'", jObject["containername"].ToString());
        }

        //开卡人
        if (jObject["starter"] != null && jObject["starter"].ToString() != "")
        {
            sql += string.Format(" AND e2.fullname = '{0}'", jObject["starter"].ToString());
        }

        //排产人
        if (jObject["planer"] != null && jObject["planer"].ToString() != "")
        {
            sql += string.Format(" AND e.fullname = '{0}'", jObject["planer"].ToString());
        }

        //完成日期
        if (jObject["plannedfinishdate"] != null && jObject["plannedfinishdate"].ToString() != "")
        {
            JArray arr = JArray.Parse(jObject["plannedfinishdate"].ToString());
            if (arr.Count == 2)
            {
                sql += string.Format(" AND TRUNC(csd.plannedfinishdate) >= TO_DATE('{0}','yyyy-mm-dd')", arr[0].ToString());
                sql += string.Format(" AND TRUNC(csd.plannedfinishdate) <= TO_DATE('{0}','yyyy-mm-dd')", arr[1].ToString());
            }
        }
        return sql;
    }

    //查询方法
    Result getData(JObject jObject)
    {
        string sql = @"
SELECT c.productname,c.productdescription,c.containerno,c.containername,c.workcentername,c.currentspec,MAX(c.stepplancreatedate) stepplancreatedate,c.stepplancreateemployeename,c.startPerson,
       SUM(c.status) delayCount,SUM(c.isFinish) finishCount,SUM(c.isDelayFinish) delayFinishCount
FROM 
(
{0}
) c
GROUP BY c.productname,c.productdescription,c.containerno,c.containername,c.workcentername,c.currentspec,c.stepplancreateemployeename,c.startPerson
ORDER BY c.containername";
        sql = string.Format(sql, getSearchSqlString(jObject));
        uMESPagingDataDTO dto = dal.GetPagingDataIns(sql, (int)jObject["currentPage"], (int)jObject["pageSize"]);
        DataTable dt = dto.DBTable;
        foreach (DataColumn col in dt.Columns)
        {
            col.ColumnName = col.ColumnName.ToLower();
        }
        Result r = new Result();
        r.data = dt;
        r.total = Convert.ToInt32(dto.RowCount);
        r.meta = new Result.Meta(200, "请求成功");
        return r;
    }

    Result getCompute(JObject jObject)
    {
        Result r = new Result();
        string sql = getSearchSqlString(jObject);
        DataTable dtDetail = dal.GetDataTable(sql);
        DataTable dtCompute = new DataTable();
        dtCompute.Columns.Add("productCount");//排产零件数
        dtCompute.Columns.Add("specCount"); //排产工序数
        dtCompute.Columns.Add("finishSpecCount");//完工工序数
        dtCompute.Columns.Add("delayfinishedSum");//超期完工数
        dtCompute.Columns.Add("delaySpecCount");// 超期工序数
        dtCompute.Columns.Add("delaySum");//超期天数

        dtCompute.Columns.Add("notFinishCount");//未完工序数
        dtCompute.Columns.Add("finishRate");//完成率
        dtCompute.Columns.Add("delayFinishRate");//超期完成率
        dtCompute.Columns.Add("intimeFinishRate");// 按时完成率

        dtCompute.Columns.Add("intimeFinishCount");//按时完工数
        dtCompute.Columns.Add("notFinishRate");//未完成率

        DataRow dr = dtCompute.NewRow();

        DataTable dtProductCount = dtDetail.DefaultView.ToTable(true, "productname");
        //计算排产零件数
        if (dtProductCount.Rows.Count > 0)
        {
            dr["productCount"] = dtProductCount.Rows.Count;
        }
        else
        {
            dr["productCount"] = 0;
        }

        //计算排产工序数
        dr["specCount"] = dtDetail.Rows.Count;

        //计算完工工序数
        DataRow[] drFinish = dtDetail.Select("checkDate IS NOT NULL ");
        if (drFinish.Length > 0)
        {
            dr["finishSpecCount"] = drFinish.Length;
        }
        else
        {
            dr["finishSpecCount"] = 0;
        }

        //计算超期完工数
        DataRow[] drDelayFinish = dtDetail.Select("checkDate IS NOT NULL and delaydays > 0 ");
        if (drDelayFinish.Length > 0)
        {
            dr["delayfinishedSum"] = drDelayFinish.Length;
        }
        else
        {
            dr["delayfinishedSum"] = 0;
        }

        //计算超期天数
        double delaySum = 0;
        if (dtDetail.Rows.Count > 0)
        {
            delaySum = Convert.ToDouble(dtDetail.Compute("Sum(delaydays)", ""));
        }
        else
        {
            dr["delaySum"] = 0;
        }

        if (delaySum < 0)
        {
            delaySum = 0;
        }
        dr["delaySum"] = delaySum;

        //计算超期工序数
        DataRow[] drDelaySpecCount = dtDetail.Select("delaydays > 0");
        if (drDelaySpecCount.Length > 0)
        {
            dr["delaySpecCount"] = drDelaySpecCount.Length;
        }
        else
        {
            dr["delaySpecCount"] = 0;
        }

        // 计算未完工工序数
        dr["notFinishCount"] = Convert.ToInt32(dr["specCount"].ToString()) - Convert.ToInt32(dr["finishSpecCount"].ToString());

        // 完工工序数
        int finishSpecCount = Convert.ToInt32(dr["finishSpecCount"].ToString());

        // 工序总数
        int specCount = Convert.ToInt32(dr["specCount"].ToString());

        // 计算完成率
        if (specCount > 0)
        {
            decimal finishRate = ((decimal)finishSpecCount / specCount) * 100;// 完成率 = 完工工序数 / 工序总数
            dr["finishRate"] = finishRate.ToString("0.00");
        }
        else
        {
            dr["finishRate"] = "--";
        }

        //超期完工数
        int delayFinishCount = Convert.ToInt32(dr["delayfinishedSum"].ToString());

        if (specCount > 0)
        {
            //超期完成率
            decimal delayFinishRate = ((decimal)delayFinishCount / specCount) * 100;// 超期完成率 = 超期完成率 / 工序总数
            dr["delayFinishRate"] = delayFinishRate.ToString("0.00");

            //按时完成率
            decimal intimeFinishRate = ((decimal)(finishSpecCount - delayFinishCount) / specCount) * 100;
            dr["intimeFinishRate"] = intimeFinishRate.ToString("0.00");
        }
        else
        {
            dr["delayFinishRate"] = "--";
            dr["intimeFinishRate"] = "--";
        }

        //按时完工数 = 完工工序数 - 超期完工数
        dr["intimeFinishCount"] = Convert.ToInt32(dr["finishSpecCount"]) - Convert.ToInt32(dr["delayfinishedSum"]);

        //未完成率 = 100% - 完成率
        if (dr["finishRate"].ToString() != "--")
        {
            dr["notFinishRate"] = 100 - Convert.ToDecimal(dr["finishRate"]);
        }
        else
        {
            dr["notFinishRate"] = "--";
        }

        dtCompute.Rows.Add(dr);
        r.data = dtCompute;
        r.meta = new Result.Meta(200, "请求成功");
        return r;
    }

    //导出
    void exportExcel(JObject jObject)
    {
        string sql = @"
SELECT c.productname,c.productdescription,c.containerno,c.containername,c.workcentername,c.currentspec,MAX(c.stepplancreatedate) stepplancreatedate,c.stepplancreateemployeename,c.startPerson,
       SUM(c.status) delayCount,SUM(c.isFinish) finishCount,SUM(c.isDelayFinish) delayFinishCount
FROM 
(
{0}
) c
GROUP BY c.productname,c.productdescription,c.containerno,c.containername,c.workcentername,c.currentspec,c.stepplancreateemployeename,c.startPerson";
        sql = string.Format(sql, getSearchSqlString(jObject));
        DataTable dtResult = dal.GetDataTable(sql);
        foreach (DataColumn col in dtResult.Columns)
        {
            col.ColumnName = col.ColumnName.ToLower();
        }
        dtResult.Columns["productname"].Caption = "零件图号";
        dtResult.Columns["productdescription"].Caption = "零件名称";
        dtResult.Columns["containerno"].Caption = "批次号";
        dtResult.Columns["containername"].Caption = "流水卡号";
        dtResult.Columns["currentspec"].Caption = "当前工序";
        dtResult.Columns["stepplancreatedate"].Caption = "排产时间";
        dtResult.Columns["stepplancreateemployeename"].Caption = "排产人";
        dtResult.Columns["startperson"].Caption = "开卡人";
        dtResult.Columns["workcentername"].Caption = "责任工区";
        dtResult.Columns["delaycount"].Caption = "超期工序数";
        dtResult.Columns["finishcount"].Caption = "完工工序数";
        dtResult.Columns["delayfinishcount"].Caption = "超期完工数";
        ExportHelper.ExportExcel(dtResult, "工序排产完成情况报表");
    }
}